package program;
import java.sql.*;
import java.util.Calendar;

public class DatabaseConnection {

	/**
	 * @param args
	 */
	static Connection conn;
	static Statement stmt;
	static ResultSet result;
	
	ResultSet ExecuteQuery(String query)
	{
		
		try
		{
			 result = stmt.executeQuery(query);
		}
		catch (SQLException e)
		{
			System.out.println("SQLException at DatabaseConneciton->DatabaseConnection()");
			System.out.println(e.getMessage());
			System.out.println(e.toString());
			//System.out.println(e.);
		}
		return result;
	}
	
	
	public static void InitializeDatabaseConnection()
	{
		System.out.println("Database connection");
		try
		{
			Class.forName("org.h2.Driver");
		    conn = DriverManager.
		      getConnection("jdbc:h2:./database2", "sa", "");
		    
		    stmt = conn.createStatement();
		    result = stmt.executeQuery("select sysdate from dual");
		    result.next();
		    System.out.println("INITIALIZE DATABASE CONNECTION");
		    
		    
		}
		catch (SQLException e)
		{
			System.out.println("SQLException at DatabaseConneciton->DatabaseConnection()");
			System.out.println(e.getMessage());
			System.out.println(e.toString());
			//System.out.println(e.);
		}
		catch (ClassNotFoundException e)
		{
			System.out.println("ClassNotFoundExcepiton at DatabaseConneciton->DatabaseConnection()");
		}
		
		
	}
	
	public static void NewFile()
	{
		try
		{
			stmt.executeUpdate("drop table if exists LINE");
			stmt.executeUpdate("drop table if exists USECASE");
			stmt.executeUpdate("drop table if exists ARROW");
			stmt.executeUpdate("drop table if exists COLOR");
			stmt.executeUpdate("drop table if exists TEXT");
			
			
			stmt.executeUpdate("create table if not exists LINE (ID INT primary key  AUTO_INCREMENT, BEGINX INT, BEGINY INT, ENDX INT, ENDY INT)");
			stmt.executeUpdate("create table if not exists USECASE (ID INT primary key  AUTO_INCREMENT, POSX INT, POSY INT, WIDTH INT, HEIGHT INT, NAME VARCHAR, NAMESIZE INT)");
			stmt.executeUpdate("create table if not exists ARROW " +
					"(ID INT primary key AUTO_INCREMENT, BEGINX INT, BEGINY INT, LEFTX INT, LEFTY INT, RIGHTX INT, RIGHTY INT, ENDX INT, ENDY INT, FILLED BOOLEAN)");
			stmt.executeUpdate("create table if not exists COLOR " +
								"(ID INT primary key AUTO_INCREMENT, R INT, G INT, B INT)");
			stmt.executeUpdate("create table if not exists TEXT " +
								"(ID INT primary key AUTO_INCREMENT, CAPTION VARCHAR, COLORID INT, POSX INT, POSY INT, SIZE INT)");
			System.out.println("DatabaseConnection->NewFile() CREATING TABLES OK");
		}
		catch(SQLException e)
		{
			e.printStackTrace();
			System.out.println("DatabaseConnection->NewFile() EXCEPTION");	
		}
	}
	

	public static void AddLine(String line)
	{
		PrintAllLines();
		
		int affectedrow = -10;
		String query = "insert into Line (BEGINX , BEGINY , ENDX , ENDY ) values("+ line + ")";
		System.out.println("Zapytanie: " + query);
		try 
		{
			
			affectedrow = stmt.executeUpdate(query);
			

		} 
		catch (SQLException e) 
		{
		
			System.out.println("DBConn->AddLine() Exception");
			e.printStackTrace();
		}
		System.out.println("Rows changed: " + affectedrow);
		PrintAllLines();
	}
	
	
	public static void AddArrow(String line)
	{
		int affectedrow = -10;
		String query = "insert into ARROW (BEGINX , BEGINY , LEFTX , LEFTY , RIGHTX  RIGHTY , ENDX  ENDY , FILLED ) values("+ line + ")";
		System.out.println("Zapytanie: " + query);
		try 
		{
			
			affectedrow = stmt.executeUpdate(query);
			

		} 
		catch (SQLException e) 
		{
		
			System.out.println("DBConn->AddLine() Exception");
			e.printStackTrace();
		}
		System.out.println("Rows changed: " + affectedrow);
	}
	
	public static void AddUseCase(String usecase)
	{
		PrintAllLines();
		
		int affectedrow = -10;
		String query = "insert into USECASE (POSX , POSY , WIDTH, HEIGHT, NAME, NAMESIZE) values("+ usecase + ")";
		System.out.println("Zapytanie: " + query);
		try 
		{
			
			affectedrow = stmt.executeUpdate(query);
			

		} 
		catch (SQLException e) 
		{
		
			System.out.println("DBConn->AddUseCase() Exception");
			e.printStackTrace();
		}
		System.out.println("Rows changed: " + affectedrow);
		PrintAllUseCase();
	}
	
	public static void AddColor(String color)
	{
		PrintAllLines();
		
		int affectedrow = -10;
		String query = "insert into COLOR (R , G , B) values("+ color + ")";
		System.out.println("Zapytanie: " + query);
		try 
		{
			
			affectedrow = stmt.executeUpdate(query);
			

		} 
		catch (SQLException e) 
		{
		
			System.out.println("DBConn->AddUseCase() Exception");
			e.printStackTrace();
		}
		System.out.println("Rows changed: " + affectedrow);
		PrintAllUseCase();
	}
	
	public static void AddText(String color)
	{
		PrintAllLines();
		
		int affectedrow = -10;
		String query = "insert into TEXT (CAPTION , COLOR , POSX, POSY, SIZE) values("+ color + ")";
		System.out.println("Zapytanie: " + query);
		try 
		{
			
			affectedrow = stmt.executeUpdate(query);
			

		} 
		catch (SQLException e) 
		{
		
			System.out.println("DBConn->AddUseCase() Exception");
			e.printStackTrace();
		}
		System.out.println("Rows changed: " + affectedrow);
		PrintAllUseCase();
	}
	
	
	public static void PrintAllLines()
	{
		System.out.println("PrintAllLines()");
		try 
		{
			result = stmt.executeQuery("select * from LINE");
			while (result.next())
			{
				String r = "";
				for (int i = 1; i <= 5; ++i)
					r += ((Integer)result.getInt(i)).toString() + " ";
				System.out.println(r);
			}
		} 
		catch (SQLException e) 
		{
			System.out.println("DatabaseConnection->PrintAllLines EXCEPTION");
		}

	}
	public static void PrintAllUseCase()
	{
		System.out.println("PrintAllUseCase()");
		try 
		{
			result = stmt.executeQuery("select * from USECASE");
			while (result.next())
			{
				String r = "";
				for (int i = 1; i <= 4; ++i)
					r += ((Integer)result.getInt(i)).toString() + " ";
				r += result.getString(5);
				System.out.println(r);
			}
		} 
		catch (SQLException e) 
		{
			System.out.println("DatabaseConnection->PrintAllLines EXCEPTION");
		}

	}
	
	
	public static void CloseConnection()
	{
		try
		{
			result.close();
		    stmt.close();
		    conn.close();
		}
		catch(SQLException e)
		{
			System.out.println("DatabaseConnection->CloseConnection");
			e.printStackTrace();
		}
	}

}
